package com.zs.util;

import com.zs.bean.User;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * Created by zhangshuqing on 2017/12/2.
 */

public class XlsUtil {

    private static Logger logger = LoggerFactory.getLogger(XlsUtil.class);

    public static List<User> readUserList(InputStream inputStream, String fileName,String code) throws IOException {
        if (fileName.endsWith(".xls")) {
            return readUserList2003(inputStream,code);
        } else {
            return readUserList2007(inputStream,code);
        }
    }

    public static List<User> readUserList2003(InputStream inputStream,String code) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);//读取第一个sheet
        int rowNum = sheet.getLastRowNum();
        logger.info("总行数{}", rowNum);
        HSSFRow row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        logger.info("标题总列数{}", colNum);
        // 正文内容应该从第二行开始,第一行为表头的标题
        List<User> userList = new ArrayList<>();
        for (int i = 1; i <= rowNum; i++) {
            HSSFRow readRow = sheet.getRow(i);
            String userName = getValue(readRow.getCell(0));
            String phone = getValue(readRow.getCell(1));
            logger.info("用户名：{}，电话：{}", userName, phone);
            User user = new User(Util.getUUID(), userName, phone,code);
            userList.add(user);
        }
        return userList;
    }

    public static List<User> readUserList2007(InputStream inputStream,String code) throws IOException {
        XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = xwb.getSheetAt(0);
        int rowNum = sheet.getLastRowNum();
        logger.info("总行数{}", rowNum);

        XSSFRow row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        logger.info("标题总列数{}", colNum);
        List<User> userList = new ArrayList<>();
        for (int i = 1; i <= rowNum; i++) {
            XSSFRow readRow = sheet.getRow(i);
            String userName = getValue(readRow.getCell(0));
            String phone = getValue(readRow.getCell(1));
            logger.info("用户名：{}，电话：{}", userName, phone);
            User user = new User(Util.getUUID(), userName, phone,code);
            userList.add(user);
        }

        return userList;
    }




    private static  String getValue(XSSFCell xssfRow) {
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            DecimalFormat df = new DecimalFormat("0");
            return df.format(xssfRow.getNumericCellValue());
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }


    private  static String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            DecimalFormat df = new DecimalFormat("0");
            return df.format(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }
}
